﻿CREATE PROCEDURE [util].[DuplicateRecord](
	@FQTableName sysname,
	-- example: <col name="">value</col>
	@SurrogateColumnsXml xml
)
AS
BEGIN
	SET NOCOUNT ON;

	/***********************************************************************
	GET METADATA
	************************************************************************/
	-- get the name of the db
	DECLARE @DbName sysname = PARSENAME(@FQTableName, 3);
	IF (@DbName IS NULL)
		THROW 50001, N'@FQTableName must be a 3-part object name', 1;

	-- get the surrogate column name value pairs
	DECLARE @SurrogateColumnList TABLE([name] sysname, [value] nvarchar(128), [pos] int IDENTITY(1,1) NOT NULL);
	INSERT INTO @SurrogateColumnList
	SELECT
		cols.col.value('(./@name)[1]','sysname') AS 'name',
		cols.col.value('(./text())[1]','sysname') AS 'value'
	FROM @SurrogateColumnsXml.nodes('//col') AS cols(col);

	-- get the column names
	DECLARE @ColumnNameList TABLE([name] sysname, [pos] int, [pk] bit);
	DECLARE @ColumnNameQuery nvarchar(2000) =
		N'SELECT c.[COLUMN_NAME], c.[ORDINAL_POSITION], (CASE WHEN (i.[COLUMN_NAME] IS NOT NULL) THEN 1 ELSE 0 END)
		FROM [' + @DbName + N'].INFORMATION_SCHEMA.COLUMNS AS c
		LEFT JOIN [' + @DbName + N'].INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i
			ON (i.[TABLE_SCHEMA] = c.[TABLE_SCHEMA]) AND (i.[TABLE_NAME] = c.[TABLE_NAME]) AND (i.[COLUMN_NAME] = c.[COLUMN_NAME])
		WHERE (c.[TABLE_SCHEMA] = ''' + PARSENAME(@FQTableName, 2) + N''') AND (c.[TABLE_NAME] = ''' + PARSENAME(@FQTableName, 1) + N''')';
	INSERT INTO @ColumnNameList
	EXEC(@ColumnNameQuery);

	/***********************************************************************
	DELETE EXISTING RECORD
	************************************************************************/
	-- get the filter for the substitute key values
	DECLARE @SurrogateColumnFilter nvarchar(2000) = (
		SELECT
			(CASE WHEN (surr.[pos] = 1) THEN N'' ELSE N' AND' END) +
			N'([' + surr.[name] + N'] = ''' + surr.[value] + N''')'
		FROM @SurrogateColumnList AS surr
		INNER JOIN @ColumnNameList AS cols
			ON (cols.[name] = surr.[name])
			AND(cols.[pk] = 1)
		FOR XML PATH('')
	);

	-- execute the delete query
	DECLARE @DeleteQuery nvarchar(2000) = 
		N'DELETE ' + @FQTableName +
		N' WHERE ' + @SurrogateColumnFilter;
	EXEC(@DeleteQuery);
	PRINT CONCAT(@@ROWCOUNT,' records deleted from ', @FQTableName);

	/***********************************************************************
	INSERT NEW RECORD
	************************************************************************/
	-- get the insert column value list, substituting the primary key values
	DECLARE @InsertColumnList nvarchar(max) = (
		SELECT
			(CASE WHEN (cols.[pos] = 1) THEN N'' ELSE N',' END) +
			(CASE
				WHEN (surr.[name] IS NOT NULL)
				THEN surr.[value]
				ELSE N'[' + cols.[name] + N']'
			END)
		FROM @ColumnNameList AS cols
		LEFT JOIN @SurrogateColumnList AS surr
			ON (surr.[name] = cols.[name])
		FOR XML PATH('')
	);

	-- execute the insert query
	DECLARE @InsertQuery nvarchar(2000) = 
		N'INSERT INTO ' + @FQTableName +
		N' SELECT TOP(1) ' + @InsertColumnList +
		N' FROM ' + @FQTableName;
	EXEC(@InsertQuery);
	PRINT CONCAT(@@ROWCOUNT,' records inserted into ', @FQTableName);
END